# !/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
@作者 杨阳
@时间 2018-06-01
@说明 自动生成代码
@注意事项 需要自生行添加外键
"""
import json
import pickle

from flask_login import current_user
from sqlalchemy.sql.functions import current_time

from model.SystemModel import TUserModel
from util import DateUtil
from util.JsonUtil import JsonEncoder
from util.StrUtil import get_uuid
from util.ThreadPluginsInitUtil import db, scheduler_obj


class ApschedulerJobs(db.Model):
    """
    定时任务使用的表，这个表里存储的是正在调试中的任务
    与apscheduler_jobs表对应的实体类
    """
    __tablename__ = 'apscheduler_jobs'
    id = db.Column(db.String(36), primary_key=True, index=True, nullable=False, comment='''主键''')
    next_run_time = db.Column(db.Float, comment='''定时任务下一次运行时间''')
    job_state = db.Column(db.LargeBinary, comment='''存储一时任务的一些信息，使用python中pickle库序列化''')

    @staticmethod
    def query_entity_grid():
        entity_list = db.session.query(TSchedulerModel.id,
                                       TSchedulerModel.comment,
                                       TSchedulerModel.create_time,
                                       TUserModel.name,
                                       ApschedulerJobs.next_run_time,
                                       ApschedulerJobs.job_state) \
            .join(TUserModel, TSchedulerModel.creator == TUserModel.id) \
            .join(ApschedulerJobs, ApschedulerJobs.id == TSchedulerModel.id) \
            .order_by(ApschedulerJobs.next_run_time)

        list = []
        for obj in entity_list:
            job_state = obj[5]
            next_time = obj[4]
            if job_state is not None:
                job_state = pickle.loads(obj[5])
            if next_time is not None:
                next_time = DateUtil.long_to_datetime(obj[4])

            list.append({
                "id": obj[0],
                "comment": obj[1],
                "create_time": obj[2],
                "creator": obj[3],
                "next_run_time": next_time,
                "job_state": job_state
            })
        retu = {"success": True, "grid": list}
        result = json.dumps(retu, cls=JsonEncoder)
        return result


class TSchedulerModel(db.Model):
    """
    与t_scheduler表对应的实体类
    """
    __tablename__ = 't_scheduler'
    id = db.Column(db.String(36), primary_key=True, index=True, nullable=False, comment='''主键''')
    comment = db.Column(db.String(255), nullable=False, comment='''定时任务说明''')
    status = db.Column(db.Integer, nullable=False, comment='''执行状态  0.未执行 1.执行中 2.执行成功 3.执行失败''')
    is_enable = db.Column(db.Integer, nullable=False, comment='''调度状态 0.未在调度中 1.已经在调度中''')
    interval = db.Column(db.String(255), nullable=False, comment='''运行频率的表达式''')
    execute_method = db.Column(db.String(255), nullable=False, comment='''要执行方法的名称''')
    group_id = db.Column(db.String(36), nullable=False, comment='''所属机构''')
    error_msg = db.Column(db.TEXT, nullable=True, comment='''如果是执行出现异常，会记录在表中''')
    create_time = db.Column(db.DateTime, nullable=True, comment='''创建时间''')
    creator = db.Column(db.String(36), nullable=True, comment='''创建人''')

    @staticmethod
    def get_entity_byId(id):
        """
        根据id获得实体类对象
        :param   id  实体类id
        """
        entity = TSchedulerModel.query.filter(TSchedulerModel.id == id).first()
        return entity

    @staticmethod
    def delete_entity_byId(id):
        """
        根据id删除数据
        :param   id  实体类id
        """
        try:
            entity = TSchedulerModel.get_entity_byId(id)

            if entity.is_enable == 1:
                result = {"success": False, "msg": "定时任务为启用状态，不可以删除!"}
            else:
                db.session.delete(entity)
                db.session.commit()

                # 为了保险再将任务从调度中移除一次
                schedul = scheduler_obj.get_job(id)
                if schedul is not None:
                    schedul.remote()

                result = {"success": True, "msg": "删除成功!"}
        except Exception as err:
            db.session.rollback()
            result = {"success": True, "msg": "删除失败，请重试!"}
        finally:
            pass
        return json.dumps(result)

    @staticmethod
    def add_entity(comment, status, interval, execute_method, group_id):
        """
        增加实体类
        :param   comment  定时任务说明
        :param   status  状态： 0.未执行 1.执行中 2.执行成功 3.执行失败
        :param   interval  执行间隔，应该是执行频率的表达式
        :param   execute_method  要执行方法的名称
        :param   group_id  所属机构
        """
        try:
            entity = TSchedulerModel()
            entity.id = get_uuid()
            entity.comment = comment
            entity.status = status
            entity.interval = interval
            entity.execute_method = execute_method
            entity.group_id = group_id
            entity.create_time = db.cast(current_time(), db.DateTime)
            entity.creator = current_user.id
            db.session.add(entity)
            db.session.commit()
            result = {"success": True, "msg": "增加成功!"}
        except Exception as err:
            db.session.rollback()
            result = {"success": True, "msg": "增加失败，请重试!"}
        finally:
            pass
        return json.dumps(result)

    @staticmethod
    def update(model):
        try:
            entity = TSchedulerModel.get_entity_byId(model.id)
            entity = model
            db.session.commit()
        except Exception as err:
            db.session.rollback()
        finally:
            pass

    @staticmethod
    def update_entity(id, comment, interval, execute_method):
        """
        修改数据库
        :param   id  主键
        :param   comment  定时任务说明
        :param   interval  执行间隔，应该是执行频率的表达式
        :param   execute_method  要执行方法的名称
        """
        try:
            entity = TSchedulerModel.get_entity_byId(id)
            entity.id = id
            entity.comment = comment
            # 修改信息后启用状态改为未启用
            entity.is_enable = 0
            # 执行状态改为未执行
            entity.status = 0
            entity.interval = interval
            entity.execute_method = execute_method

            db.session.commit()
            result = {"success": True, "msg": "修改定时任务成功!"}
        except Exception as err:
            db.session.rollback()
            result = {"success": True, "msg": "修改定时任务失败，请重试!"}
        finally:
            pass
        return json.dumps(result)

    @staticmethod
    def query_entity_grid(group_id, search_comment, search_status, search_interval, search_execute_method,
                          search_start_create_time, search_end_create_time, current_page=1):
        """
        查询表格数据
        """
        filters = {1 == 1}
        if search_comment is not None and search_comment != '':
            filters.add(TSchedulerModel.comment == search_comment)
        if search_status is not None and search_status != '':
            filters.add(TSchedulerModel.status == search_status)
        if search_interval is not None and search_interval != '':
            filters.add(TSchedulerModel.interval == search_interval)
        if search_execute_method is not None and search_execute_method != '':
            filters.add(TSchedulerModel.execute_method == search_execute_method)
        if search_start_create_time is not None and search_start_create_time != '':
            filters.add(TSchedulerModel.create_time >= search_start_create_time)
        if search_end_create_time is not None and search_end_create_time != '':
            filters.add(TSchedulerModel.create_time <= search_end_create_time)
        if group_id is not None and group_id != '':
            filters.add(TSchedulerModel.group_id == group_id)
        # 查询数据集合
        entity_list = db.session.query(TSchedulerModel.id,
                                       TSchedulerModel.comment,
                                       TSchedulerModel.status,
                                       TSchedulerModel.interval,
                                       TSchedulerModel.execute_method,
                                       TSchedulerModel.create_time,
                                       TUserModel.name,
                                       TSchedulerModel.error_msg,
                                       TSchedulerModel.is_enable) \
            .filter(*filters) \
            .join(TUserModel, TSchedulerModel.creator == TUserModel.id) \
            .order_by(TSchedulerModel.create_time.desc()) \
            .limit(10) \
            .offset((int(current_page) - 1) * 10)
        # 查询总记录数
        total = db.session.query(TSchedulerModel.id).filter(*filters).count()
        list = []
        for obj in entity_list:
            list.append({
                "id": obj[0],
                "comment": obj[1],
                "status": obj[2],
                "interval": obj[3],
                "execute_method": obj[4],
                "create_time": obj[5],
                "creator": obj[6],
                "error_msg": obj[7],
                "is_enable": obj[8]
            })
        retu = {"success": True, "grid": list, "total_count": total}
        result = json.dumps(retu, cls=JsonEncoder)
        return result


def execute_sql(sql, params):
    db.session.execute(sql, params)
    db.session.commit()
